package com.b2c.repository.erp;

import com.alibaba.fastjson.JSONObject;
import com.b2c.entity.result.PagingResponse;
import com.b2c.entity.ErpOrderItemEntity;
import com.b2c.entity.ErpStockLocationBatchAddVo;
import com.b2c.entity.ErpStockLocationEntity;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;
import com.b2c.repository.Tables;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * 描述：
 * 仓库Repository
 *
 * @author qlp
 * @date 2019-03-21 18:15
 */
@Repository
public class ErpStockLocationRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    private static Logger log = LoggerFactory.getLogger(ErpStockLocationRepository.class);
    /**
     * 获取仓库列表
     *
     * @return
     */
    public List<ErpStockLocationEntity> getListByParentId(Integer parentId) {
        String sql = "SELECT * FROM " + Tables.ErpStockLocation + " WHERE isDelete=0 AND parentId=? ";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), parentId);
    }

    /**
     * 根据层级  获取仓库列表
     *
     * @param depth
     * @return
     */
//    public List<ErpStockLocationEntity> getListByDepth(Integer depth) {
//        String sql = "SELECT * FROM " + Tables.ErpStockLocation + " WHERE isDelete=0 AND depth=? ";
//        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), depth);
//    }


    public List<ErpOrderItemEntity> localtionNumber() {
       String sql = "SELECT s.id,s.specNumber,s.color_value,s.size_value,sl.number locationName,egsi.currentQty,s.goodsId productId,egsi.locationId outLocationId from erp_goods_spec s LEFT JOIN erp_goods_stock_info egsi ON s.id =egsi.specId\n" +
               "LEFT JOIN  erp_stock_location  sl on sl.id=egsi.locationId\n" +
               " WHERE egsi.isDelete=0 AND egsi.currentQty>0  AND left(sl.number,3)='A21'  ORDER BY sl.number  ";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpOrderItemEntity.class));
    }

    /**
     * 根据层级获取仓库列表（分页）
     *
     * @param pageIndex
     * @param pageSize
     * @param depth
     * @param number
     * @return
     */
    @Transactional
    public PagingResponse<ErpStockLocationEntity> getListByDepth(Integer pageIndex, Integer pageSize, Integer depth,String number) {
//        pageIndex = (pageIndex - 1) * pageSize;
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT SQL_CALC_FOUND_ROWS a.*");
        if (depth == 2) {
            //查二级，带出一级
            sb.append(",b.name as name1,b.id as parentId1 ");
            sb.append(" FROM ").append(Tables.ErpStockLocation).append(" a ");
            sb.append(" LEFT JOIN ").append(Tables.ErpStockLocation).append(" b on b.id=a.parentId ");
        } else if (depth == 3) {
            sb.append(",b.name as name2,b.id as parentId2 ");
            sb.append(",c.name as name1,c.id as parentId1 ");
            sb.append(" FROM ").append(Tables.ErpStockLocation).append(" a ");
            sb.append(" LEFT JOIN ").append(Tables.ErpStockLocation).append(" b on b.id=a.parentId ");
            sb.append(" LEFT JOIN ").append(Tables.ErpStockLocation).append(" c on c.id=b.parentId ");
        } else {
            sb.append(" FROM ").append(Tables.ErpStockLocation).append(" a ");
        }
        sb.append(" WHERE a.isDelete=0 AND a.depth=? ");
        params.add(depth);

        if(StringUtils.isEmpty(number)==false) {
            sb.append(" AND a.number = ? ");
            params.add(number);
        }

        sb.append(" ORDER BY a.number desc limit ?,?");

        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
//        String sql = "SELECT SQL_CALC_FOUND_ROWS a.*,b.name as topName FROM " + Tables.ErpStoreHouse + " a " +
//                " LEFT JOIN " +Tables.ErpStoreHouse+" b on b.id = a.parentId "+
//                " WHERE a.isDelete=0 AND a.depth=? limit ?,?";

        List<ErpStockLocationEntity> list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpStockLocationEntity.class),  params.toArray(new Object[params.size()]));

        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }

    /**
     * 根据仓库名查询id
     *
     * @param name
     * @return
     */
    public Integer getIdByName(String name) {
        Integer id = 0;
        try {
            id = jdbcTemplate.queryForObject("SELECT id FROM " + Tables.ErpStockLocation + " WHERE name = ? AND isDelete=0", Integer.class, name);
        } catch (EmptyResultDataAccessException e) {
            id = 0;
        }
        return id;
    }

    /**
     * 根据仓库编码查询id
     *
     * @param number
     * @return
     */
    public Integer getIdByNumber(String number) {
        Integer id = 0;
        try {
            id = jdbcTemplate.queryForObject("SELECT id FROM " + Tables.ErpStockLocation + " WHERE number = ?", Integer.class, number);
        } catch (EmptyResultDataAccessException e) {
            id = 0;
        }
        return id;
    }

    public ErpStockLocationEntity getEntityByNumber(String number) {

        try {
            return jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpStockLocation + " WHERE number = ?", new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), number);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }

    }

    /**
     *
     * @param id
     * @return
     */
    public boolean checkStockLocationUsed(Integer id) {
        String sql = "SELECT COUNT(0) FROM erp_goods_stock_info WHERE locationId=? AND isDelete=0";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, id);
        if(count.intValue()>0) return true;//已经使用
        else return false; //没有使用
    }


    /**
     * 添加仓库信息
     *
     * @param number
     * @param name
     * @return
     */
    public Integer addStockHouse(String number, String name) {
        String insert = "INSERT INTO " + Tables.ErpStockLocation + " (number,name,parentId,depth,parentId1,parentId2,createTime) VALUES (?,?,?,?,?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, number);
            ps.setString(2, name);
            ps.setInt(3, 0);
            ps.setInt(4, 1);
            ps.setInt(5, 0);
            ps.setInt(6, 0);
            ps.setLong(7, System.currentTimeMillis() / 1000);
            return ps;
        }, keyHolder);
        return keyHolder.getKey().intValue();
    }

    /**
     * 添加库区
     */
    public Integer addReservoir(String number, String name, Integer parentId1) {
        ErpStockLocationEntity stockHouse = new ErpStockLocationEntity();
        try {
            //查询仓库
            stockHouse = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpStockLocation + " WHERE id=?", new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), parentId1);
        } catch (Exception e) {
            return -404;//仓库不存在
        }

        try {
            String insert = "INSERT INTO " + Tables.ErpStockLocation + " (number,name,parentId,depth,parentId1,parentId2,createTime) VALUES (?,?,?,?,?,?,?)";
            KeyHolder keyHolder = new GeneratedKeyHolder();
            ErpStockLocationEntity finalStockHouse = stockHouse;
            jdbcTemplate.update(connection -> {
                PreparedStatement ps = connection.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, number);
                ps.setString(2, name);
                ps.setInt(3, parentId1);
                ps.setInt(4, 2);
                ps.setInt(5, finalStockHouse.getId());
                ps.setInt(6, 0);
                ps.setLong(7, System.currentTimeMillis() / 1000);
                return ps;
            }, keyHolder);
            return keyHolder.getKey().intValue();
        } catch (Exception e) {
            return 0;
        }
    }

    /**
     * 添加仓位
     */
    public Integer addShelf(String number, String name, Integer houseId, Integer reservoirId) {
        /***查询编码是否存在***/
        try {
            var s = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpStockLocation + " WHERE number=?", new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), number);
            return -403;//仓库编码已存在
        } catch (Exception e) {
        }

        if (StringUtils.isEmpty(name)) {
            name = number;
        }

        ErpStockLocationEntity stockHouse = new ErpStockLocationEntity();
        //查询仓库
        try {
            stockHouse = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpStockLocation + " WHERE id=?", new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), houseId);
        } catch (Exception e) {
            return -404;
        }

        ErpStockLocationEntity reservoir = new ErpStockLocationEntity();
        //查询库区
        try {
            reservoir = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpStockLocation + " WHERE id=?", new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), reservoirId);
        } catch (Exception e) {
            return -405;
        }

        try {
            String insert = "INSERT INTO " + Tables.ErpStockLocation + " (number,name,parentId,depth,parentId1,parentId2,createTime) VALUES (?,?,?,?,?,?,?)";
            KeyHolder keyHolder = new GeneratedKeyHolder();

            ErpStockLocationEntity finalStockHouse = stockHouse;
            ErpStockLocationEntity finalReservoir = reservoir;
            String finalName = name;
            jdbcTemplate.update(connection -> {
                PreparedStatement ps = connection.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, number);
                ps.setString(2, finalName);
                ps.setInt(3, reservoirId);
                ps.setInt(4, 3);
                ps.setInt(5, finalStockHouse.getId());
                ps.setInt(6, finalReservoir.getId());
                ps.setLong(7, System.currentTimeMillis() / 1000);
                return ps;
            }, keyHolder);
            return keyHolder.getKey().intValue();
        } catch (Exception e) {
            System.out.println("添加仓位异常：" + e.getMessage());
            return 0;
        }
    }

    /**
     * 更新仓位
     *
     * @param id
     * @param number
     * @param name
     * @param houseId
     * @param reservoirId
     */
    public void updateShelf(Integer id, String number, String name, Integer houseId, Integer reservoirId) {
        ErpStockLocationEntity stockHouse = new ErpStockLocationEntity();
        ErpStockLocationEntity reservoir = new ErpStockLocationEntity();
        try {
            //查询仓库
            stockHouse = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpStockLocation + " WHERE id=?", new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), houseId);
        } catch (Exception e) {
            houseId = 0;
        }
        try {
            //查询库区
            reservoir = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpStockLocation + " WHERE id=?", new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), reservoirId);

        } catch (Exception e) {
            reservoirId = 0;
        }
        if (houseId == 0 || reservoirId == 0) {
            //不更新库位
            jdbcTemplate.update("UPDATE " + Tables.ErpStockLocation + " SET number=?,name=? WHERE id=?",
                    number, name, id);
        } else {


            jdbcTemplate.update("UPDATE " + Tables.ErpStockLocation + " SET number=?,name=?,parentId=?,parentId1=?,parentId2=? WHERE id=?",
                    number, name, reservoirId, houseId, reservoirId, id);
        }

    }

    /**
     * 更新库区
     *
     * @param id
     * @param number
     * @param name
     * @param houseId
     */
    public void updateReservoir(Integer id, String number, String name, Integer houseId) {
        try {
            //查询仓库
            jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpStockLocation + " WHERE id=?", new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), houseId);

//        String houseName = stockHouse.getName();
            jdbcTemplate.update("UPDATE " + Tables.ErpStockLocation + " SET number=?,name=?,parentId=?,parentId1=? WHERE id=? AND isDelete=0", number, name, houseId, houseId, id);
        } catch (Exception e) {

        }
    }

    /**
     * 更新仓库信息
     *
     * @param stockId
     * @param number
     * @param name
     */
    public void updateStockHouse(Integer stockId, String number, String name) {
        jdbcTemplate.update("UPDATE " + Tables.ErpStockLocation + " SET number=?,name=? WHERE id=? AND isDelete=0", number, name, stockId);
    }

    /**
     * 删除仓库
     *
     * @param id
     */
    public void delStockHouse(Integer id) {
        jdbcTemplate.update("UPDATE " + Tables.ErpStockLocation + " SET isDelete=1 WHERE id=? ", id);
    }

    public boolean getStockNumById(Integer id) {
        return jdbcTemplate.queryForObject("SELECT IFNULL(sum(currentQty),0) FROM " + Tables.ErpGoodsStockInfo + " WHERE locationId = ? AND isDelete=0", Integer.class, id) > 0;
    }

    /**
     * 根据Id查询仓库名
     *
     * @param id
     * @return
     */
    public String getName(Integer id) {
        return jdbcTemplate.queryForObject("select number from " + Tables.ErpStockLocation + " where id=? ", String.class, id);
    }

    /**
     * excel添加仓位
     *
     * @param shelf
     * @return
     */
    @Transactional
    public ResultVo<Integer> batchAddShelf(ErpStockLocationBatchAddVo shelf) {

        /****查询是否存在number****/
        try {
            var s = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpStockLocation + " WHERE number=?", new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), shelf.getNumber());
            return new ResultVo<>(EnumResultVo.DataExist, "仓位已存在");
        } catch (Exception e) {
        }

        ErpStockLocationEntity store = new ErpStockLocationEntity();
        ErpStockLocationEntity reservoir = new ErpStockLocationEntity();

        try {
            /********查询仓库编码********/
            store = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpStockLocation + " WHERE number=?", new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), shelf.getHouseNo());
            /********查询库区编码********/
            reservoir = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpStockLocation + " WHERE number=?", new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), shelf.getReservoirNo());
        } catch (Exception e) {
            String msg ="仓库编码"+shelf.getHouseNo()+"或库区编码"+shelf.getReservoirNo()+"错误"+ JSONObject.toJSONString(shelf);
            log.error(msg);
            return new ResultVo<>(EnumResultVo.DataError, msg);
        }
        if (StringUtils.isEmpty(shelf.getName())) {
            shelf.setName(shelf.getNumber());
        }
        String insert = "INSERT INTO " + Tables.ErpStockLocation + " (number,name,parentId,parentId1,parentId2,depth,createTime) VALUES (?,?,?,?,?,?,?)";
        jdbcTemplate.update(insert, shelf.getNumber(), shelf.getName(), reservoir.getId(), store.getId(), reservoir.getId(), 3, System.currentTimeMillis() / 1000);

        return new ResultVo<>(EnumResultVo.SUCCESS);
    }


    /**
     * 查询没有被使用的库位(排除自己使用的)
     * 库存不为0则视为已使用
     *
     * @param reservoirId
     * @return
     */
//    public List<ErpStockLocationEntity> getShelfByReservoirIdAndNotUsed(Integer reservoirId, Integer selfLocationId) {
//        //查询库区
//        String sql = "SELECT s.* FROM " + Tables.ErpStockLocation + " s WHERE (s.id not in (SELECT locationId FROM " + Tables.ErpGoodsStockInfo + " WHERE currentQty<>0 ) OR s.id = ?) AND s.isDelete=0 and s.parentId = ? ";
//        List<ErpStockLocationEntity> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), selfLocationId, reservoirId);
//        return query;
//    }

    /**
     * 根据编号查询仓库list
     *
     * @param pageSize
     * @param number
     * @return
     */
    public List<ErpStockLocationEntity> getListByNumber(Integer pageSize,Integer depth, String number) {
        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT a.*");

        if (depth == 2) {
            //查二级，带出一级
            sb.append(",b.name as name1,b.id as parentId1 ");
            sb.append(" FROM ").append(Tables.ErpStockLocation).append(" a ");
            sb.append(" LEFT JOIN ").append(Tables.ErpStockLocation).append(" b on b.id=a.parentId ");
        } else if (depth == 3) {
            sb.append(",b.name as name2,b.id as parentId2 ");
            sb.append(",c.name as name1,c.id as parentId1 ");
            sb.append(" FROM ").append(Tables.ErpStockLocation).append(" a ");
            sb.append(" LEFT JOIN ").append(Tables.ErpStockLocation).append(" b on b.id=a.parentId ");
            sb.append(" LEFT JOIN ").append(Tables.ErpStockLocation).append(" c on c.id=b.parentId ");
        } else {
            sb.append(" FROM ").append(Tables.ErpStockLocation).append(" a ");
        }


//        sb.append(",b.name as name2,b.id as parentId2 ");
//        sb.append(",c.name as name1,c.id as parentId1 ");
//        sb.append(" FROM ").append(Tables.ErpStockLocation).append(" a ");
//        sb.append(" LEFT JOIN ").append(Tables.ErpStockLocation).append(" b on b.id=a.parentId ");
//        sb.append(" LEFT JOIN ").append(Tables.ErpStockLocation).append(" c on c.id=b.parentId ");
        sb.append(" WHERE a.isDelete=0 ");
        sb.append(" AND a.depth=? ");
        sb.append(" AND a.number like (?) ");
        sb.append(" ORDER BY a.number ASC limit ?");//AND a.number=?

        List<Object> params = new ArrayList<>();
        params.add(depth);
        params.add( number + "%");
        params.add(pageSize);

        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), params.toArray(new Object[params.size()]));
    }
}
